package com.ph.irp.base.assistantmaterial.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.base.assistantmaterial.dao.PBaseAssistantmaterialAreaDAO;
import com.ph.irp.model.PBaseAssistantmaterialArea;
import com.ph.util.DBUtil;

public class PBaseAssistantmaterialAreaDAOImpl implements
		PBaseAssistantmaterialAreaDAO {

	/**
	 * 
	 */
	private static final long serialVersionUID = -254683887434077837L;
	private ComboPooledDataSource dataSource;

	public void setDataSource(ComboPooledDataSource dataSource) {

		this.dataSource = dataSource;
	}

	@Override
	public void addAssistantmaterialArea(PBaseAssistantmaterialArea area)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			con = dataSource.getConnection();
			pStmt = con
					.prepareStatement("insert into p_base_assistantmaterial_area(AreaCode, AreaName) values(?, ?)");
			pStmt.setString(1, area.getAreaCode());
			pStmt.setString(2, area.getAreaName());
			pStmt.execute();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

	}

	@Override
	public void deleteAssistantmaterialArea(String areaId) throws Throwable {

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			con = dataSource.getConnection();
			pStmt = con
					.prepareStatement("delete from p_base_assistantmaterial_area where areaId = ?");
			int id = Integer.valueOf(areaId);
			pStmt.setInt(1, id);
			pStmt.execute();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

	}

	@Override
	public List<PBaseAssistantmaterialArea> getAllAssistantmaterialAreas()
			throws Throwable {

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		List<PBaseAssistantmaterialArea> areas = null;

		try {
			con = dataSource.getConnection();
			pStmt = con
					.prepareStatement("select * from p_base_assistantmaterial_area");
			rs = pStmt.executeQuery();
			areas = this.turnResultSetToList(rs);
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return areas;

	}

	@Override
	public PBaseAssistantmaterialArea getAssistantmaterialAreaById(int areaId)
			throws Throwable {

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		PBaseAssistantmaterialArea area = new PBaseAssistantmaterialArea();

		try {
			con = dataSource.getConnection();
			pStmt = con
					.prepareStatement("select * from p_base_assistantmaterial_area where AreaID = ?");
			pStmt.setInt(1, areaId);
			rs = pStmt.executeQuery();
			if (rs.next()) {
				area.setAreaId(rs.getInt("AreaID"));
				area.setAreaCode(rs.getString("AreaCode"));
				area.setAreaName(rs.getString("AreaName"));
			} else
				area = null;
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return area;

	}

	@Override
	public List<PBaseAssistantmaterialArea> getAssistantmaterialAreasByCodeAndName(
			String areaCode, String areaName) throws Throwable {

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		List<PBaseAssistantmaterialArea> areas = null;

		try {
			con = dataSource.getConnection();
			pStmt = con
					.prepareStatement("select * from p_base_assistantmaterial_area where AreaCode like ? and AreaName like ?");
			pStmt.setString(1, "%" + areaCode + "%");
			pStmt.setString(2, "%" + areaName + "%");
			rs = pStmt.executeQuery();
			areas = turnResultSetToList(rs);
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return areas;

	}

	@Override
	public List<PBaseAssistantmaterialArea> getAssistantmaterialAreasByCode(
			String areaCode) throws Throwable {

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		List<PBaseAssistantmaterialArea> areas = null;

		try {
			con = dataSource.getConnection();
			pStmt = con
					.prepareStatement("select * from p_base_assistantmaterial_area where AreaCode like ?");
			pStmt.setString(1, "%" + areaCode + "%");
			rs = pStmt.executeQuery();
			areas = turnResultSetToList(rs);
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return areas;

	}

	@Override
	public List<PBaseAssistantmaterialArea> getAssistantmaterialAreasByName(
			String areaName) throws Throwable {

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		List<PBaseAssistantmaterialArea> areas = null;

		try {
			con = dataSource.getConnection();
			pStmt = con
					.prepareStatement("select * from p_base_assistantmaterial_area where AreaName like ?");
			pStmt.setString(1, "%" + areaName + "%");
			rs = pStmt.executeQuery();
			areas = turnResultSetToList(rs);
			;
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return areas;

	}

	@Override
	public void updateAssistantmaterialArea(PBaseAssistantmaterialArea area)
			throws Throwable {

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			con = dataSource.getConnection();
			pStmt = con
					.prepareStatement("update p_base_assistantmaterial_area set AreaCode = ?, AreaName = ? where AreaID = ?");
			pStmt.setString(1, area.getAreaCode());
			pStmt.setString(2, area.getAreaName());
			pStmt.setInt(3, area.getAreaId());
			pStmt.execute();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

	}

	public List<PBaseAssistantmaterialArea> turnResultSetToList(ResultSet rs)
			throws SQLException {
		List<PBaseAssistantmaterialArea> areas = new ArrayList<PBaseAssistantmaterialArea>();
		while (rs.next()) {
			PBaseAssistantmaterialArea area = new PBaseAssistantmaterialArea();
			area.setAreaId(rs.getInt("AreaID"));
			area.setAreaCode(rs.getString("AreaCode"));
			area.setAreaName(rs.getString("AreaName"));
			areas.add(area);
		}
		return areas;
	}

	/*
	 * @Override public boolean isSameCode(String areaCode) throws Throwable {
	 * Connection con = null; PreparedStatement pStmt = null; ResultSet rs =
	 * null; try { con = dataSource.getConnection(); pStmt =
	 * con.prepareStatement("select * from p_base_assistantmaterial_area where
	 * AreaCode = ?"); pStmt.setString(1, areaCode); rs = pStmt.executeQuery();
	 * List<PBaseAssistantmaterialArea> areas = this.turnResultSetToList(rs);
	 * if(areas.size() != 0) return true; } catch (Exception e) { throw new
	 * Exception(e.getMessage()); } finally { try { DBUtil.setNullObject(con,
	 * pStmt, rs); } catch (Exception e) { throw new Exception(e.getMessage()); } }
	 * return false; }
	 * 
	 * @Override public boolean isSameName(String areaName) throws Throwable {
	 * Connection con = null; PreparedStatement pStmt = null; ResultSet rs =
	 * null; try { con = dataSource.getConnection(); pStmt =
	 * con.prepareStatement("select * from p_base_assistantmaterial_area where
	 * AreaName = ?"); pStmt.setString(1, areaName); rs = pStmt.executeQuery();
	 * List<PBaseAssistantmaterialArea> areas = this.turnResultSetToList(rs);
	 * if(areas.size() != 0) return true; } catch (Exception e) { throw new
	 * Exception(e.getMessage()); } finally { try { DBUtil.setNullObject(con,
	 * pStmt, rs); } catch (Exception e) { throw new Exception(e.getMessage()); } }
	 * return false; }
	 */

}
